USE [UFDATA_001_2024]
GO
/****** Object:  StoredProcedure [dbo].[SaveStockIn4MO]    Script Date: 2024-06-19 14:53:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*******说明*********
本存储过程可以根据单号，生产订单子件ID，创建人，仓库，数量等信息来创建一个只有一行明细的材料出库单，并进行审核
其中创建人、仓库、行ID以及数量等合法性，需要由调用者来保证正确。
真实部署时，应当修改cAcc_Id='001'为实际账套号。
本程序不支持以下要素
1.批号
2.序列号
3.自由项
4.多单位
5.出库跟踪入库
6.预留
本程序经过以下测试，但请务必在客户环境上重新进行下列测试
1.现存量报表测试
2.审批，取消审批，删单测试
3.存货核算记账测试
4.本程序与原生程序交叉制单测试（即测试3张单，分别从用本程序，原生程序来测试同一料号）
5.新开料号与旧料号分别测试
6.新开仓库与旧仓库分别测试
********************/

ALTER PROCEDURE [dbo].[SaveStockIn4OEM]
--@cCode varchar(100),
@cCode1 int,@cMaker varchar(100),@cWhCode varchar(100),@MoDId int,@iQuantity decimal,@cMemo varchar(100), @msg varchar(500) output
AS
BEGIN

DECLARE @cCode VARCHAR(100)
SET @cCode = CONVERT(VARCHAR(100), @cCode1 + 1000000) 
SET @cCode = CONVERT(VARCHAR(6),GETDATE(),12) + @cCode  --加上年月日，比如240625 ，如果把VARCHAR(6)改成VARCHAR（4），则只取2406
SET @cCode = 'MD'+@cCode   --加上前缀
--此时@cCode应该是MD202425100012345 ,如果你的AUTOID = 12345的话

DECLARE @ACCID VARCHAR(100)
DECLARE @FATHERID VARCHAR(100)
DECLARE @CHILDID VARCHAR(100)
DECLARE @TODAY DATETIME
DECLARE @NOW DATETIME
DECLARE @COSTING BIT
DECLARE @cInvCode VARCHAR(100)
DECLARE @cDepCode VARCHAR(100)
DECLARE @iMQuantity DECIMAL
DECLARE @iNQuantity DECIMAL
DECLARE @cPsPcode VARCHAR(100)
DECLARE @cMoPode VARCHAR(100)
DECLARE @iMoSeq varchar(20)
DECLARE @MOID INT
DECLARE @iOpSeq varchar(20)
DECLARE @iPeSoDid INT
DECLARE @iPeSoSeq INT
DECLARE @cPeSoCode varchar(100)
DECLARE @APPSTATUS INT
DECLARE @TMP INT
DECLARE @BOM INT
DECLARE @ITAXRATE DECIMAL
DECLARE @CVENCODE VARCHAR(100)
DECLARE @iunitprice DECIMAL (16,6)
DECLARE @inatunitprice DECIMAL(16,6)
DECLARE @itaxprice DECIMAL(16,6)
DECLARE @IEXCHRATE DECIMAL(16,6)
DECLARE @IEXCHNAME VARCHAR(100)
SET @ACCID = '001' --真实环境应当修改这个
SELECT @TODAY = CONVERT(DATETIME, CONVERT(CHAR(20), GETDATE(), 110))
SELECT @NOW = GETDATE()
SELECT @COSTING=bInCost FROM Warehouse WHERE @cWhCode = cWhCode ;
IF EXISTS(SELECT 1 FROM RdRecord01 WHERE cCode = @cCode)
BEGIN
SET @MSG = '单号重复'
RETURN 1 
END

SELECT @cInvCode = A.cInvCode,@cDepCode=b.cDepCode , 
@cMoPode = B.ccode ,@iNQuantity=A.iquantity,@iMoSeq = a.ivouchrowno ,@CVENCODE=B.cVenCode,
@iMQuantity = a.iquantity,@MOID=A.MOID,@BOM = A.BOMID,@ITAXRATE=A.IPERTAXRATE,@iunitprice=A.IUNITPRICE,
@inatunitprice=A.inatunitprice,@itaxprice=A.inatunitprice,@IEXCHNAME = B.cexch_name,@IEXCHRATE= 1,
@APPSTATUS=CASE 
	WHEN  ISNULL(ireceivedqty,0)+@iQuantity > iquantity THEN 1 --单据剩余可入量不足
	WHEN  CSTATE != 1 THEN 2 --订单状态不是已审核
	END
FROM OM_MODETAILS A LEFT JOIN OM_MOMAIN B ON A.MOID = B.MOID WHERE A.MODetailsID = @MoDId

IF (@cInvCode IS NULL)
BEGIN
SET @MSG = '不存在这个产品'
RETURN 2 
END 
IF (@APPSTATUS = 1)
BEGIN
SET @MSG = '单据剩余可入量不足'
RETURN 3 
END
IF (@APPSTATUS = 2)
BEGIN
SET @MSG = '订单状态不是已审核'
RETURN 4
END 
IF NOT EXISTS(SELECT 1 FROM Warehouse WHERE cWhCode = @cWhCode)
BEGIN
SET @MSG = '不存在该仓库'
RETURN 8 
END 

IF NOT EXISTS(SELECT 1 FROM  UFSystem.dbo.UA_Account WHERE cAcc_Id = @ACCID)
BEGIN
SET @MSG = 'ACCid配置错误'
RETURN 9 
END 
BEGIN TRANSACTION
--处理PK
SELECT @FATHERID=(1+iFatherId),@CHILDID=(1+iChildId) 
	FROM UFSystem.dbo.UA_Identity WITH(NOLOCK) WHERE cAcc_Id = @ACCID AND cVouchType = 'rd';
UPDATE UFSystem.dbo.UA_Identity SET iFatherId = @FATHERID,iChildId = @CHILDID WHERE cAcc_Id = @ACCID AND cVouchType = 'rd';
--写入材料出库单
INSERT INTO RdRecord01(ID,bRdFlag,cVouchType,cBusType,cSource,cWhCode,dDate,cCode,cMaker,cHandler,dVeriDate,VT_ID,dnmaketime,dnverifytime,cDepCode,cOrderCode ,ipurorderid,cVenCode,iTaxRate ,iExchRate ,CEXCH_NAME,cmemo)
VALUES(@FATHERID+1000000000,1,'01','委外加工','委外订单',@cWhCode,@TODAY,@cCode,@cMaker,@cMaker,@TODAY,27,@NOW,@NOW,@cDepCode,@cMoPode,@MOID,@CVENCODE,@ITAXRATE,@IEXCHRATE,@IEXCHNAME,@cmemo);
IF @@ROWCOUNT != 1
BEGIN
SET @MSG = 'SQL执行INSERT INTO RdRecord01错误'
ROLLBACK TRANSACTION
RETURN 101
END
--写入入库单明细
INSERT INTO RdRecords01(AutoID,ID,cInvCode,iQuantity,irowno,bCosting,iNQuantity,iorderdid,isotype,brelated,CPOID,iOMoDID,iTaxRate,chVencode,bTaxCost,iprocessfee,iprocesscost) 
VALUES(@CHILDID+1000000000,@FATHERID+1000000000,@cInvCode,@iQuantity,1,@COSTING,@iQuantity,0,0,0,@MOID,@MoDId,@ITAXRATE,@CVENCODE,1,@inatunitprice*@iQuantity,@inatunitprice);
IF @@ROWCOUNT != 1
BEGIN
SET @MSG = 'SQL执行INSERT INTO RdRecords01错误'
ROLLBACK TRANSACTION
RETURN 102
END
--更新仓库现存量，如果需要支持其他维度如批次维度等，需要在这里进行评估

IF EXISTS(SELECT 1 FROM CurrentStock WHERE cInvCode = @cInvCode AND cWhCode = @cWhCode)
BEGIN
	UPDATE CurrentStock set iQuantity = iQuantity + @iQuantity WHERE cInvCode = @cInvCode AND cWhCode = @cWhCode
	IF @@ROWCOUNT != 1
	BEGIN
	SET @MSG = 'SQL执行UPDATE CurrentStock错误' --如果某个仓库有多个批次或者自由项记录则会报这个错误
	ROLLBACK TRANSACTION
	RETURN 103
	END
END
ELSE 
BEGIN
	IF NOT EXISTS(SELECT 1 FROM SCM_Item WHERE cInvCode = @cInvCode )
	BEGIN
		INSERT INTO SCM_Item(cInvCode,PartId) VALUES(@cInvCode,0);
		SET @TMP = @@IDENTITY
	END
	ELSE
	BEGIN
	    SELECT @TMP = ID FROM SCM_ITEM WHERE cInvCode = @cInvCode
	END

    INSERT INTO CurrentStock(cWhCode,cInvCode,ItemId,iQuantity,iSodid) VALUES(@cWhCode,@cInvCode,@TMP ,@iQuantity,'');
	IF @@ROWCOUNT != 1
	BEGIN
	SET @MSG = 'SQL执行INSERT CurrentStock错误'
	ROLLBACK TRANSACTION
	RETURN 104
	END
END

--回写对应的生产订单发料记录
UPDATE OM_MODETAILS  SET ireceivedqty = ISNULL(ireceivedqty,0) + @iQuantity WHERE MODetailsID = @modid
IF @@ROWCOUNT != 1
BEGIN
SET @MSG = 'SQL执行UPDATE mom_modetail错误' --如果某个仓库有多个批次或者自由项记录则会报这个错误
ROLLBACK TRANSACTION
RETURN 105
END
SET @msg = @cCode
COMMIT TRANSACTION
END